﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using Newtonsoft.Json;
using System.Configuration;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using Microsoft.JScript;



namespace TradeWeb.Controllers
{
    public class ProviderController : Controller
    {
        //
        // GET: /Client/

        public ActionResult Index()
        {
            return View();
        }
        //查询供应商信息列表－[分页+模糊查询]－－－－－[注意：供应商与客户使用同一张表]
        public string GetClientListPages(string strUserCode, string strPageIndex, string strPageSizes,string strSearchWhere)
        {
            string jsonResult = string.Empty;
            //获取用户权限
            //获取销售合同数据
            //是否需要排序
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            try
            {
                //打开数据库连接
                sqlConn.Open();
                DataSet dsResult = new DataSet();


                string strWhere = string.Empty;
                //添加模糊查询的条件------查询记录总数目的时候，也要添加模糊查询的条件。（模糊查询与分页互不干扰）
                if (!string.IsNullOrEmpty(strSearchWhere.Trim()))
                {
                    strSearchWhere = strSearchWhere.Trim();
                    strSearchWhere = Microsoft.JScript.GlobalObject.decodeURIComponent(strSearchWhere);
                    string strSearch = " ( ClientCode LIKE '%" + strSearchWhere + "%' OR ClientName LIKE '%" + strSearchWhere + "%' OR ClientSource LIKE '%" + strSearchWhere + "%' OR CNUserName like '%" + strSearchWhere + "%' )";
                    strWhere += strSearch;
                }
                if (!string.IsNullOrEmpty(strWhere))
                {
                    strWhere = " and " + strWhere;
                }


                //查询记录的数目
                string strSqlCount = "SELECT COUNT(*) Row_Count FROM V_Client1 where  ISDel=0  AND (Creater=@UserCode or UserCode=@UserCode or charindex('|@UserCode|', '|'+AllowReader)>0 or '是'='是') " + strWhere;
                SqlCommand sqlComCount;
                SqlDataAdapter sqlAdaCount;
                sqlComCount = new SqlCommand(strSqlCount, sqlConn);
                sqlComCount.Parameters.Add("@UserCode",SqlDbType.VarChar,50);
                sqlComCount.Parameters["@UserCode"].Value = strUserCode;

                sqlAdaCount = new SqlDataAdapter(sqlComCount);
                sqlAdaCount.Fill(dsResult, "T_Count");

                //查询列表的数据
                string strSqlResult = "SELECT * FROM (SELECT ROW_NUMBER() over(order by ClientCode) as colnum,ISDel,ClientCode,CreateDate,ISUse,ISPotentialClient,ClassName,JPClientName,ClientLevel,ShortName,ClientName,Tels,Faxs,EMail,ProductClassName,CNCountry,Address,ClassID,ClientSource,DeptCode,CNDeptName,UserCode,CNUserName,Creater,CreaterName FROM V_Client1  where  ISDel=0  AND (Creater=@UserCode or UserCode=@UserCode or charindex('|@UserCode|', '|'+AllowReader)>0 or '是'='是') " + strWhere + " ) tab_1";
                SqlCommand sqlComResult;
                SqlDataAdapter sqlAdaResult;

                //添加分页的条件
                if (string.IsNullOrEmpty(strPageSizes))
                {
                    strPageSizes = "10";
                }
                string strSearchPage = string.Empty;
                if (!string.IsNullOrEmpty(strPageIndex))
                {
                    strSearchPage = " WHERE colnum>(" + strPageIndex + "-1)*" + strPageSizes + " AND colnum<=" + strPageIndex + "*" + strPageSizes + "";
                }
                if (!string.IsNullOrEmpty(strSearchPage))
                {
                    strSqlResult = strSqlResult + strSearchPage;
                }


                sqlComResult = new SqlCommand(strSqlResult, sqlConn);
                sqlComResult.Parameters.Add("@UserCode", SqlDbType.VarChar, 50);
                sqlComResult.Parameters["@UserCode"].Value = strUserCode;

                sqlAdaResult = new SqlDataAdapter(sqlComResult);
                sqlAdaResult.Fill(dsResult, "T_Client");

                sqlConn.Close();
                jsonResult = "{\"success\":true  ,\"data\":" + JsonConvert.SerializeObject(dsResult) + "  ,\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }

        //查询供应商详细信息
        public string GetClient(string strPKValue)
        {
            string jsonResult = string.Empty;
            List<string> lstSQL = new List<string>();
            List<string> lstTableName = new List<string>();
            string strSQL = string.Empty;
            string strTableName = string.Empty;
            strPKValue = strPKValue.Trim();

            //基本信息
            strSQL = "SELECT aa.*,(Select ClassName From T_ClientClass1 where T_ClientClass1.ClassID=aa.ClassID) as ClassName FROM T_Client aa WHERE  aa.ClientCode='" + strPKValue + "' AND aa.ISDel=0";
            strTableName = "T_Client";
            lstSQL.Add(strSQL);
            lstTableName.Add(strTableName);

            //联系人信息
            strSQL = "SELECT * FROM T_Contact WHERE ClientCode='" + strPKValue + "' AND ISDel=0";
            strTableName = "T_Contact";
            lstSQL.Add(strSQL);
            lstTableName.Add(strTableName);

            //银行信息
            strSQL = "SELECT * FROM T_ClientBank WHERE ClientCode='"+strPKValue+"' AND ISDel=0";
            strTableName = "T_ClientBank";
            lstSQL.Add(strSQL);
            lstTableName.Add(strTableName);

            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlDataAdapter sqlAdapter;
            DataSet dsList = new DataSet();
            try
            {
                sqlConn.Open();
                for (int i = 0; i < lstSQL.Count; i++)
                {
                    sqlAdapter = new SqlDataAdapter(lstSQL[i], sqlConn);
                    sqlAdapter.Fill(dsList, lstTableName[i]);
                }
                sqlConn.Close();
                string strDstoJson = JsonConvert.SerializeObject(dsList);
                jsonResult = "{\"success\":true  ,\"data\":" + strDstoJson + "  ,\"message\":\"正常\"}";
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }
    }
}
